USE master

DROP DATABASE CelestialDB
GO

CREATE DATABASE CelestialDB
GO

USE CelestialDB
GO
/*********************************** TABLES ******************************************/
/* Users */
CREATE TABLE tblUsers(
	Email		VARCHAR(50) PRIMARY KEY
)
/*-----------------------------------------------------------------------------------*/
/* Employees */
CREATE TABLE tblEmployees(
	
	EmployeeID INT IDENTITY(1000, 1) PRIMARY KEY NOT NULL,
	[Password] VARBINARY(16) NOT NULL,
	UserLevel INT,
	EmployedOn DATE NOT NULL,
	
	FirstName VARCHAR(30),
	LastName VARCHAR(30),
	Contact VARCHAR(20),
	Email VARCHAR(50), --uniqueness enforced in tblUsers
	
	DOB DATE,
	AddressLine1 VARCHAR(30),
	AddressLine2 VARCHAR(30),
	PostalCode VARCHAR(10),
	
	IsActive BIT
	
	FOREIGN KEY(Email) REFERENCES tblUsers(Email)
)
GO
/*-----------------------------------------------------------------------------------*/
/* Customers */
CREATE TABLE tblCustomers(
	
	CustomerID INT IDENTITY(100000,1) PRIMARY KEY, --Format:1#####
	[Password] VARBINARY(16) NOT NULL,
	UserLevel INT NOT NULL,
	RegisteredOn DATE,
	
	FirstName VARCHAR(30),
	LastName VARCHAR(30), 
	Email VARCHAR(50),--uniqueness enforced in tblUsers
	Contact VARCHAR(20),
	DOB DATE,
	Country CHAR(2),
	City VARCHAR(30),
	[State] VARCHAR(30),
	AddressLine1 VARCHAR(30),
	AddressLine2 VARCHAR(30),
	PostalCode VARCHAR(10),
	
	CreditCardNumber VARCHAR(20),
	CreditCardType INT,
	CreditCardCVV VARCHAR(6),
	CreditCardExpiry DATE,
	CreditCardHolder VARCHAR(30),
	
	IsActive BIT
	
	FOREIGN KEY(Email) REFERENCES tblUsers(Email)
)
GO
/*-----------------------------------------------------------------------------------*/
/* Product Categories */
CREATE TABLE tblCategories(

	CategoryName VARCHAR(30) PRIMARY KEY,
	ParentCategory VARCHAR(30) DEFAULT(NULL) 
	
	FOREIGN KEY(ParentCategory) REFERENCES tblCategories(CategoryName)
		ON DELETE NO ACTION ON UPDATE NO ACTION --use trigger
)
/*-----------------------------------------------------------------------------------*/
/* Products */
CREATE TABLE tblProducts(

	ProductID CHAR(8) PRIMARY KEY,	--Format: [a-z]{3}#####
	ProductName VARCHAR(30) UNIQUE NOT NULL,   
	Category VARCHAR(30),
	[Description] TEXT,
	
	Materials VARCHAR(60),
	WoodFinish VARCHAR(30),
	[Length] DECIMAL(18,2),
	Width DECIMAL(18,2),
	Height DECIMAL(18,2),
	Diameter DECIMAL(18,2),
	
	Image1 VARCHAR(100), --path of image
	
	Price DECIMAL(18,2) DEFAULT(0.00),
	Units INT DEFAULT(0),	
	
	IsActive BIT NOT NULL DEFAULT(1),
	
	FOREIGN KEY(Category) REFERENCES tblCategories(CategoryName)
		ON DELETE SET NULL ON UPDATE CASCADE
)
/*-----------------------------------------------------------------------------------*/
/* Cart */
CREATE TABLE Cart(

	CustomerID INT, 
	ProductID CHAR(8) NOT NULL,
	Quantity INT NOT NULL,
	UnitPrice DECIMAL(18,2) NOT NULL,
	LastUpdated DATETIME
	
	PRIMARY KEY(CustomerID, ProductID)
	
	FOREIGN KEY(CustomerID) REFERENCES tblCustomers(CustomerID)
		ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY(ProductID) REFERENCES tblProducts(ProductID)
		ON DELETE CASCADE ON UPDATE CASCADE
)
GO
/*-----------------------------------------------------------------------------------*/
/* Orders */
CREATE TABLE tblOrders(

	OrderID UNIQUEIDENTIFIER PRIMARY KEY,
	CustomerID INT NOT NULL,
	EmployeeID INT,   --The employee who dispatch the order
					  --will be null before dispatch
	TransactionID UNIQUEIDENTIFIER NOT NULL,
	OrderDate DATETIME NOT NULL,
	DispatchDate DATETIME,
	[Status] INT DEFAULT(0), /* 0 - Pending - awaiting dispatch date to be set
								1 - Processed - dispatch date set
								2 - Dispatched - sent for shipping
								3 - Closed - delivery acknowledged
								9 - Cancelled									*/
	BillingCountry CHAR(2),
	BillingCity VARCHAR(30),
	BillingState VARCHAR(30),
	BillingAddressLine1 VARCHAR(30),
	BillingAddressLine2 VARCHAR(30),
	BillingPostalCode VARCHAR(10),
	
	ShippingCountry CHAR(2),
	ShippingCity VARCHAR(30),
	ShippingState VARCHAR(30),
	ShippingAddressLine1 VARCHAR(30),
	ShippingAddressLine2 VARCHAR(30),
	ShippingPostalCode VARCHAR(10),

	Remarks VARCHAR(100)	
				 
	FOREIGN KEY(CustomerID) REFERENCES tblCustomers(CustomerID)
		ON DELETE CASCADE ON UPDATE CASCADE,
)
GO
/*-----------------------------------------------------------------------------------*/
/* Order Items */
CREATE TABLE tblOrderItems(

	OrderItemID UNIQUEIDENTIFIER,
	OrderID UNIQUEIDENTIFIER,
	ProductID CHAR(8),
	Quantity INT NOT NULL,
	UnitPrice DECIMAL(18,2) NOT NULL,
	PriceOverride BIT DEFAULT(0) NOT NULL,
	Remarks VARCHAR(100)
	
	PRIMARY KEY(OrderItemID)
	
	FOREIGN KEY(OrderID) REFERENCES tblOrders(OrderID)
		ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY(ProductID) REFERENCES tblProducts(ProductID)
		ON UPDATE NO ACTION ON DELETE NO ACTION
)
GO
/*-----------------------------------------------------------------------------------*/

/**************************** TRIGGERS ***********************************************/
/* Insert customer or employee only if email is universally unique */
CREATE TRIGGER tgrInsertCustomer
ON tblCustomers
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @Email VARCHAR(50)
	SELECT @Email = Email FROM inserted i
	
	IF (SELECT COUNT(*) FROM tblUsers WHERE Email = @Email) = 0
	BEGIN
		BEGIN TRANSACTION
			BEGIN TRY
				--Insert into tblUsers first
				INSERT INTO tblUsers VALUES(@Email)
				
				--Insert into tblCustomers
				INSERT INTO tblCustomers SELECT [Password], UserLevel, RegisteredOn, 
				FirstName, LastName, Email, Contact, DOB, Country, City, [State],
				AddressLine1, AddressLine2, PostalCode, CreditCardNumber,
				CreditCardType, CreditCardCVV, CreditCardExpiry, CreditCardHolder,
			    IsActive FROM inserted i
			END TRY
			BEGIN CATCH
				ROLLBACK TRANSACTION
				RAISERROR('Failed to insert customer: %s', 18, 2, @Email)
			END CATCH
			
		COMMIT TRANSACTION
	END
	ELSE
		RAISERROR('Email %s is already being used', 18, 2, @Email) 
END

GO
/*-----------------------------------------------------------------------------------*/
CREATE TRIGGER tgrInsertEmployee
ON tblEmployees
INSTEAD OF INSERT
AS
BEGIN
	DECLARE @Email VARCHAR(50)
	SELECT @Email = Email FROM inserted i

	IF (SELECT COUNT(*) FROM tblUsers WHERE Email = @Email) = 0
	BEGIN
		BEGIN TRANSACTION
			BEGIN TRY
				--Insert into tblUsers first
				INSERT INTO tblUsers VALUES(@Email)
				
				--Insert into tblCustomers
				INSERT INTO tblEmployees SELECT [Password], UserLevel, EmployedOn,
				FirstName, LastName, Contact, Email, DOB, AddressLine1,
				AddressLine2,PostalCode, IsActive FROM inserted i
			END TRY
			BEGIN CATCH
				ROLLBACK TRANSACTION
				RAISERROR('Failed to insert employee: %s', 18, 2, @Email)
			END CATCH
			
		COMMIT TRANSACTION
	END
	ELSE
		RAISERROR('Email %s is already being used', 18, 2, @Email) 
END
GO
/*-----------------------------------------------------------------------------------*/
CREATE TRIGGER tgrDeleteCustomer
ON tblCustomers
AFTER DELETE 
AS
BEGIN
	DECLARE @Email VARCHAR(50)
	SELECT @Email = d.Email FROM deleted d
	DELETE FROM tblUsers WHERE Email = @Email 
END

GO
/*-----------------------------------------------------------------------------------*/
CREATE TRIGGER tgrDeleteEmployee
ON tblEmployees
AFTER DELETE 
AS
BEGIN
	DECLARE @Email VARCHAR(50)
	SELECT @Email = d.Email FROM deleted d
	DELETE FROM tblUsers WHERE Email = @Email 
END

GO
/*-----------------------------------------------------------------------------------*/
/*CREATE TRIGGER tgrUpdateCustomer
ON tblCustomers
INSTEAD OF UPDATE
AS
BEGIN
	DECLARE @OldEmail VARCHAR(50)
	DECLARE @NewEmail VARCHAR(50)
	
	SELECT @OldEmail = d.Email FROM deleted d
	SELECT @NewEmail = i.Email FROM inserted i
	
	IF @OldEmail <> @NewEmail
	BEGIN
		IF (SELECT COUNT(*) FROM tblUsers WHERE Email = @NewEmail) > 0
		BEGIN	
			RAISERROR('Email is already registered: %s', 18, 2, @NewEmail)
			RETURN
		END
		ELSE
			--insert new email in tblUsers
			INSERT INTO tblUsers VALUES(@NewEmail)	
	END
	
	--now update tblCustomers can pass...
	UPDATE tblCustomers SET Email = i.Email, UserLevel = i.UserLevel, 
	RegisteredOn = i.RegisteredOn, FirstName = i.FirstName, LastName = i.LastName,
	Contact = i.Contact, Country = i.Country, City = i.City, 
	[State] = i.[State], Addressline1 = i.AddressLine1, AddressLine2 = i.AddressLine2, 
	PostalCode = i.PostalCode, CreditCardNumber = i.CreditCardNumber,
	CreditCardtype = i.CreditCardType, CreditCardCVV = i.CreditCardCVV, 
	CreditCardExpiry = i.CreditCardExpiry, CreditCardHolder = i.CreditCardHolder 
	FROM (SELECT Email, UserLevel, RegisteredOn, FirstName, LastName, Contact,
	Country, City, [State], AddressLine1, AddressLine2, PostalCode, CreditCardNumber,
	CreditCardType, CreditCardCVV, CreditCardExpiry, CreditCardHolder FROM inserted) AS i
	WHERE tblCustomers.Email = @OldEmail
	
	--delete old email from tblUsers
	DELETE FROM tblUsers WHERE Email = @OldEmail
END*/
GO
/******************************* VIEWS ***********************************************/
/* Users view */
CREATE VIEW vUsers 
AS
(
	SELECT Email, EmployeeID AS ID, [Password], UserLevel, FirstName, LastName, IsActive FROM tblEmployees
	UNION
	SELECT Email, CustomerID AS ID, [Password] , UserLevel, Firstname, LastName, IsActive FROM tblCustomers
)	
GO
/*-----------------------------------------------------------------------------------*/ 
/* Shows all descendent categories of all categories */
CREATE VIEW vSubCategories
AS
--NULL is the root ancestor 
WITH cteAllDescendents(Ancestor, Parent, Child)
AS
(
	SELECT c.ParentCategory AS Ancestor, c.ParentCategory AS Parent, c.CategoryName AS Child
	FROM tblCategories c
	INNER JOIN tblCategories p ON c.ParentCategory = p.CategoryName
		
	UNION ALL
	SELECT p2.Ancestor, c2.ParentCategory AS Parent, c2.CategoryName AS Child FROM tblCategories c2 
	INNER JOIN cteAllDescendents p2 ON c2.ParentCategory = p2.Child
)
SELECT * FROM cteAllDescendents

GO
/*-----------------------------------------------------------------------------------*/
/* Compute extended price of each order item */
CREATE VIEW vExtendedOrderItems
AS
	SELECT OrderItemID, OrderID, ProductID, Quantity, UnitPrice, 
		   (UnitPrice * Quantity) AS ExtendedPrice, PriceOverride, Remarks 
	FROM tblOrderItems
GO
/*-----------------------------------------------------------------------------------*/
/* Compute net amount for each order */
CREATE VIEW vExtendedOrders
AS
WITH cteNetAmount(OrderID, NetAmount)
AS
(
	SELECT tblOrders.OrderID, SUM(ExtendedPrice) AS NetAmount
	FROM tblOrders, vExtendedOrderItems
	WHERE tblOrders.OrderID = vExtendedOrderItems.OrderID
	GROUP BY tblOrders.OrderID
)

SELECT tblOrders.OrderID, CustomerID, EmployeeID, TransactionID, OrderDate, 
		   DispatchDate, [Status], NetAmount, Remarks FROM tblOrders 
INNER JOIN cteNetAmount ON tblOrders.OrderID =  cteNetAmount.OrderID 

GO
/*-----------------------------------------------------------------------------------*/
CREATE VIEW vCustomerPurchaseHistory
AS	-- requires DISTINCT; same product appearing in multiple orders
	SELECT DISTINCT tblOrders.CustomerID, ProductID FROM tblOrderItems, tblOrders, tblCustomers
	WHERE tblOrders.OrderID = tblOrderItems.OrderID AND
	tblOrders.CustomerID = tblCustomers.CustomerID	
GO
/*-----------------------------------------------------------------------------------*/
/* Number of similar products for each pair of customers */
CREATE VIEW vDegrees
AS
	SELECT A.CustomerID AS CustomerA, B.CustomerID AS CustomerB, COUNT(A.ProductID) AS Degree 
	FROM vCustomerPurchaseHistory A, vCustomerPurchaseHistory B
	WHERE A.ProductID = B.ProductID AND 
	A.CustomerID <> B.CustomerID --skip same customer
	GROUP BY A.CustomerID, B.CustomerID
GO
/*-----------------------------------------------------------------------------------*/
/* Shows number of units sold */
CREATE VIEW vExtendedProducts
AS
	WITH cteUnitsSold(ProductID, Sold)
	AS
	(
		SELECT tblProducts.ProductID, SUM(Quantity) AS Sold
		FROM tblProducts, tblOrderItems
		WHERE tblProducts.ProductID = tblOrderItems.ProductID
		GROUP BY tblProducts.ProductID
	)
	SELECT tblProducts.ProductID, ProductName, Category, [Description], Materials,
	WoodFinish, [Length], Width, Height, Diameter, Image1, 
	Price, Units, cteUnitsSold.Sold, IsActive FROM tblProducts
	LEFT OUTER JOIN cteUnitsSold ON tblProducts.ProductID = cteUnitsSold.ProductID
GO
/******************************* STORED PROCEDURES ***********************************/

/*********************************** USP For Users ***********************************/
/* Validate Login */
CREATE PROCEDURE uspValidateLogin(
	@Email VARCHAR(50),
	@Password BINARY(16)
)
AS
	SELECT * FROM vUsers WHERE Email = @Email AND [Password]=@Password AND IsActive = 1
GO
/*-----------------------------------------------------------------------------------*/
/* Check if email is in database*/
CREATE PROCEDURE uspIsEmailRegistered
	@Email	VARCHAR(50)
AS
	SELECT Email FROM vUsers WHERE Email = @Email
GO
/*-----------------------------------------------------------------------------------*/
/* Check if email is taken*/
CREATE PROCEDURE uspIsEmailTaken
	@Email	VARCHAR(50),
	@ID		INT
AS
	SELECT Email FROM vUsers WHERE Email = @Email AND ID <> @ID
GO
/*-----------------------------------------------------------------------------------*/
/* Check if account is active */
CREATE PROCEDURE uspIsAccountActive
	@Email	VARCHAR(50)
AS
	SELECT Email FROM vUsers WHERE Email = @Email AND IsActive = 1
GO
/*-----------------------------------------------------------------------------------*/

/******************************** USP For Customers **********************************/
/* Insert Customer */
CREATE PROCEDURE uspInsertCustomer
	@Password			BINARY(16),
	@UserLevel			INT,
	@RegisteredOn		DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Email				VARCHAR(50),
	@Contact			VARCHAR(15),
	@DOB				DATE,
	
	@Country			CHAR(2),
	@City				VARCHAR(30),
	@State				VARCHAR(30),
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@CreditCardNumber	VARCHAR(20),
	@CreditCardType		INT,
	@CreditCardCVV		VARCHAR(6),
	@CreditCardExpiry	DATE,
	@CreditCardHolder	VARCHAR(30),
	
	@IsActive			BIT
AS
BEGIN
	INSERT INTO tblCustomers 
	VALUES(@Password, 1, @RegisteredOn, @FirstName, @LastName, @Email, @Contact, @DOB,
		   @Country, @City, @State, @AddressLine1, @AddressLine2, @PostalCode,
		   @CreditCardNumber, @CreditCardType, @CreditCardCVV, @CreditCardExpiry, @CreditCardHolder,
		   @IsActive)
END
GO
/*-----------------------------------------------------------------------------------*/
/* Update Customer */
CREATE PROCEDURE uspUpdateCustomer
	@CustomerID			INT,
	@UserLevel			INT,
	@Password			BINARY(16),
	@RegisteredOn		DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Email				VARCHAR(50),
	@Contact			VARCHAR(15),
	@DOB				DATE,
	
	@Country			CHAR(2),
	@City				VARCHAR(30),
	@State				VARCHAR(30),
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@CreditCardNumber	VARCHAR(20),
	@CreditCardType		INT,
	@CreditCardCVV		VARCHAR(6),
	@CreditCardExpiry	DATE,
	@CreditCardHolder	VARCHAR(30),
	
	@IsActive			BIT
AS
BEGIN

--BEGIN TRANSACTION
	--BEGIN TRY
		--update tblUsers first
		--UPDATE tblUsers SET Email = @Email 
		--WHERE Email = (SELECT Email FROM tblCustomers WHERE CustomerID = @CustomerID)
		
		--update customers now
		UPDATE tblCustomers 
		SET [Password] = @Password,
			UserLevel = 1,
			RegisteredOn = @RegisteredOn,
		
			FirstName = @FirstName,
			LastName = @LastName,				
			Email = @Email,
			Contact = @Contact,				
			DOB = @DOB,				
	
			Country = @Country,			
			City = @City,				
			[State] = @State,				
			AddressLine1 = @AddressLine1,		
			AddressLine2 = @AddressLine2,		
			PostalCode = @PostalCode,			
	
			CreditCardNumber = @CreditCardNumber,	
			CreditCardType = @CreditCardType,		
			CreditCardCVV = @CreditCardCVV,		
			CreditCardExpiry = @CreditCardExpiry,	
			CreditCardHolder = @CreditCardHolder,	
	
			IsActive = @IsActive	
	
			WHERE CustomerID = @CustomerID
	--END TRY
	--BEGIN CATCH
	--	ROLLBACK TRANSACTION
	--	RAISERROR('Unable to update customer: %s', 18, 2, @Email)
	--END CATCH
	
--COMMIT TRANSACTION
	
END

GO
/*-----------------------------------------------------------------------------------*/
/* Delete Customer */
CREATE PROCEDURE uspDeleteCustomer
	@CustomerID INT
AS
BEGIN
	DELETE FROM tblCustomers WHERE CustomerID=@CustomerID
END

GO
/*-----------------------------------------------------------------------------------*/
/* GetCustomer By ID */
CREATE PROCEDURE uspGetCustomerByID
	@CustomerID INT
AS
BEGIN
	SELECT * FROM tblCustomers WHERE CustomerID = @CustomerID
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetCustomer By Email */
CREATE PROCEDURE uspGetCustomerByEmail
	@Email VARCHAR(50)
AS
BEGIN
	SELECT * FROM tblCustomers WHERE Email = @Email
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetCustomers */
CREATE PROCEDURE uspGetCustomers
	@Active	INT
AS
BEGIN
	IF @Active=2
		SELECT * FROM tblCustomers
	ELSE
		SELECT * FROM tblCustomers WHERE IsActive = CONVERT(BIT, @Active)
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetNextCustomerID */
CREATE PROCEDURE uspGetNextCustomerID
AS
BEGIN
	SELECT IDENT_CURRENT('tblCustomers') + IDENT_INCR('tblCustomers')
END
GO
/*-----------------------------------------------------------------------------------*/
/* ActivateCustomer */
CREATE PROCEDURE uspActivateCustomer
	@Email	VARCHAR(50)
AS
BEGIN
	UPDATE tblCustomers SET IsActive = 1 WHERE Email = @Email
	SELECT * FROM tblCustomers WHERE Email = @Email
END
GO
/******************************** USP For Employees **********************************/
/* Insert Employee */
CREATE PROCEDURE uspInsertEmployee
	@Password			BINARY(16),
	@UserLevel			INT,
	--EmployeeID IDENTITY
	@EmployedOn			DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Contact			VARCHAR(15),
	@Email				VARCHAR(50),
	@DOB				DATE,
	
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@IsActive			BIT
AS
BEGIN
	INSERT INTO tblEmployees 
	VALUES(@Password, @UserLevel, @EmployedOn, @FirstName, @LastName, @Contact, @Email, @DOB,
		   @AddressLine1, @AddressLine2, @PostalCode, @IsActive)
END
GO

/*-----------------------------------------------------------------------------------*/
/* Update Employee */
CREATE PROCEDURE uspUpdateEmployee
	--Username PRIMARY
	@Password			BINARY(16),
	@UserLevel			INT,
	@EmployeeID			INT,
	@EmployedOn			DATE,
	
	@FirstName			VARCHAR(30),
	@LastName			VARCHAR(30),
	@Contact			VARCHAR(15),
	@Email				VARCHAR(50),
	@DOB				DATE,
	
	@AddressLine1		VARCHAR(30),
	@AddressLine2		VARCHAR(30),
	@PostalCode			VARCHAR(10),
	
	@IsActive			BIT
AS
BEGIN
	UPDATE tblEmployees 
	SET [Password] = @Password,
		UserLevel = @UserLevel,
		EmployedOn = @EmployedOn,
		
		FirstName = @FirstName,
		LastName = @LastName,	
		Contact = @Contact,				
		Email = @Email,	
		DOB = @DOB,					
		AddressLine1 = @AddressLine1,		
		AddressLine2 = @AddressLine2,		
		PostalCode = @PostalCode,			

		IsActive = @IsActive	
	
	WHERE EmployeeID = @EmployeeID
END

GO

/*-----------------------------------------------------------------------------------*/
/* Delete Employee */
CREATE PROCEDURE uspDeleteEmployee
	@EmployeeID INT
AS
BEGIN
	DELETE FROM tblEmployees WHERE EmployeeID=@EmployeeID
END

GO

/*-----------------------------------------------------------------------------------*/
/* GetEmployee By ID*/
CREATE PROCEDURE uspGetEmployeeByID
	@EmployeeID INT
AS
BEGIN
	SELECT * FROM tblEmployees WHERE EmployeeID = @EmployeeID
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetEmployee By Email*/
CREATE PROCEDURE uspGetEmployeeByEmail
	@Email VARCHAR(50)
AS
BEGIN
	SELECT * FROM tblEmployees WHERE Email = @Email
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetEmployees */
CREATE PROCEDURE uspGetEmployees
	@Active	INT
AS
BEGIN
	IF @Active=2
		SELECT * FROM tblEmployees
	ELSE
		SELECT * FROM tblEmployees WHERE IsActive = CONVERT(BIT, @Active)
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetNextEmployeeID */
CREATE PROCEDURE uspGetNextEmployeeID
AS
BEGIN
	SELECT IDENT_CURRENT('tblEmployees') + IDENT_INCR('tblEmployees')
END
GO
/*-----------------------------------------------------------------------------------*/
/* GetNextEmployeeID */
CREATE PROCEDURE uspEmailTaken
	@Email		VARCHAR(50),
	@CustomerID	INT
AS 
BEGIN
SELECT * FROM tblCustomers WHERE CustomerID <> @CustomerID AND Email = @Email 
END
GO
/*************************************************************************************/

/****************************** USP For OrdersItems **********************************/

IF OBJECT_ID('uspGetOrderItems') IS NOT NULL DROP PROCEDURE uspGetOrderItems
IF OBJECT_ID('uspInsertOrderItem') IS NOT NULL DROP PROCEDURE uspInsertOrderItem
IF OBJECT_ID('uspUpdateOrderItem') IS NOT NULL DROP PROCEDURE uspUpdateOrderItem
IF OBJECT_ID('uspDeleteOrderItem') IS NOT NULL DROP PROCEDURE uspDeleteOrderItem
IF OBJECT_ID('uspClearOrderItems') IS NOT NULL DROP PROCEDURE uspClearOrderItems
GO
/*-----------------------------------------------------------------------------------*/
/* Get Order items */
CREATE PROCEDURE uspGetOrderItems
	@OrderID	UNIQUEIDENTIFIER
AS
	SELECT * FROM tblOrderItems WHERE OrderID = @OrderID
GO
/*-----------------------------------------------------------------------------------*/
/* Insert order item */
CREATE PROCEDURE uspInsertOrderItem
	@OrderID		UNIQUEIDENTIFIER,
	@ProductID		CHAR(8),
	@Quantity		INT,
	@UnitPrice		DECIMAL(18,2),
	@PriceOverride	BIT,
	@Remarks		VARCHAR(100)
AS
BEGIN
	INSERT INTO tblOrderItems 
	VALUES (NEWID(), @OrderID, @ProductID, @Quantity, @UnitPrice, @PriceOverride, @Remarks)
END 
GO
/*-----------------------------------------------------------------------------------*/
/* Update order item */
CREATE PROCEDURE uspUpdateOrderItem
	@OrderItemID	UNIQUEIDENTIFIER,
	@OrderID		UNIQUEIDENTIFIER,
	@ProductID		CHAR(8),
	@Quantity		INT,
	@UnitPrice		DECIMAL(18,2),
	@PriceOverride	BIT,
	@Remarks		VARCHAR(100)
AS
BEGIN
	UPDATE tblOrderItems 
	SET OrderID = @OrderID, 
		ProductID = @ProductID, 
		Quantity = @Quantity,
		UnitPrice =  @UnitPrice, 
		PriceOverride = @PriceOverride,
		Remarks = @Remarks
	WHERE OrderID = @OrderID
END 
GO
/*-----------------------------------------------------------------------------------*/
/* Delete order item */
CREATE PROCEDURE uspDeleteOrderItem
	@OrderItemID	UNIQUEIDENTIFIER
AS
	DELETE FROM tblOrderItems WHERE OrderItemID = @OrderItemID
GO
/*-----------------------------------------------------------------------------------*/
/* Clear order items */
CREATE PROCEDURE uspClearOrderItems
	@OrderID	UNIQUEIDENTIFIER
AS
	DELETE FROM tblOrderItems WHERE OrderID = @OrderID
GO

/*************************************************************************************/

/********************************* USP For Orders ************************************/

IF OBJECT_ID('uspGetOrder') IS NOT NULL DROP PROCEDURE uspGetOrder
IF OBJECT_ID('uspGetOrdersByCustomerID') IS NOT NULL DROP PROCEDURE uspGetOrdersByCustomerID
IF OBJECT_ID('uspGetOrdersByStatus') IS NOT NULL DROP PROCEDURE uspGetOrdersByStatus
IF OBJECT_ID('uspInsertOrder') IS NOT NULL DROP PROCEDURE uspInsertOrder
IF OBJECT_ID('uspUpdateOrder') IS NOT NULL DROP PROCEDURE uspUpdateOrder
IF OBJECT_ID('uspDeleteOrder') IS NOT NULL DROP PROCEDURE uspDeleteOrder
IF OBJECT_ID('uspDispatchOrder') IS NOT NULL DROP PROCEDURE uspDispatchOrder
IF OBJECT_ID('uspSetDispatchDate') IS NOT NULL DROP PROCEDURE uspSetDispatchDate
GO

/*-----------------------------------------------------------------------------------*/
/* Get order */
CREATE PROCEDURE uspGetOrder
	@OrderID	UNIQUEIDENTIFIER
AS
BEGIN
	SELECT * FROM vExtendedOrders 
	INNER JOIN vExtendedOrderItems ON vExtendedOrders.OrderID =vExtendedOrderItems.OrderID
	WHERE vExtendedOrders.OrderID = @OrderID
END
GO
/*-----------------------------------------------------------------------------------*/
/* Get orders by customer id */
CREATE PROCEDURE uspGetOrdersByCustomerID
	@CustomerID	INT
AS
BEGIN
	SELECT * FROM vExtendedOrders
	WHERE CustomerID = @CustomerID
END
GO
/*-----------------------------------------------------------------------------------*/
/* Get orders by status */
CREATE PROCEDURE uspGetOrdersByStatus
	@OrderStatus	INT
AS
BEGIN
	SELECT * FROM vExtendedOrders 
	WHERE [Status] = @OrderStatus
END
GO
/*-----------------------------------------------------------------------------------*/
/* Insert order */
CREATE PROCEDURE uspInsertOrder
	@OrderID		UNIQUEIDENTIFIER,
	@CustomerID		INT,
	@EmployeeID		INT,
	@TransactionID	UNIQUEIDENTIFIER,
	@OrderDate		DATETIME,
	@DispatchDate	DATETIME,
	
	@BillingCountry			CHAR(2),
	@BillingCity			VARCHAR(30),
	@BillingState			VARCHAR(30),
	@BillingAddressLine1	VARCHAR(30),
	@BillingAddressLine2	VARCHAR(30),
	@BillingPostalCode		VARCHAR(10),
	
	@ShippingCountry		CHAR(2),
	@ShippingCity			VARCHAR(30),
	@ShippingState			VARCHAR(30),
	@ShippingAddressLine1	VARCHAR(30),
	@ShippingAddressLine2	VARCHAR(30),
	@ShippingPostalCode		VARCHAR(10),
	
	@Remarks		VARCHAR(100)
AS
BEGIN
	INSERT INTO tblOrders
	VALUES (@OrderID, @CustomerID, @EmployeeID, @TransactionID, @OrderDate, @DispatchDate, 0, 
			@BillingCountry, @BillingCity, @BillingState, @BillingAddressLine1, @BillingAddressLine2, @BillingPostalCode, 
			@BillingCountry, @BillingCity, @BillingState, @BillingAddressLine1, @BillingAddressLine2, @BillingPostalCode, 
			@Remarks)
END
GO
/*-----------------------------------------------------------------------------------*/
/* Update order */
CREATE PROCEDURE uspUpdateOrder
	@OrderID		UNIQUEIDENTIFIER,
	@CustomerID		INT,
	@EmployeeID		INT,
	@TransactionID	UNIQUEIDENTIFIER,
	@OrderDate		DATETIME,
	@DispatchDate	DATETIME,
	
	@BillingCountry			CHAR(2),
	@BillingCity			VARCHAR(30),
	@BillingState			VARCHAR(30),
	@BillingAddressLine1	VARCHAR(30),
	@BillingAddressLine2	VARCHAR(30),
	@BillingPostalCode		VARCHAR(10),
	
	@ShippingCountry		CHAR(2),
	@ShippingCity			VARCHAR(30),
	@ShippingState			VARCHAR(30),
	@ShippingAddressLine1	VARCHAR(30),
	@ShippingAddressLine2	VARCHAR(30),
	@ShippingPostalCode		VARCHAR(10),
	
	@Remarks		VARCHAR(100)
AS
BEGIN 
	UPDATE tblOrders
	SET CustomerID = @CustomerID,
		EmployeeID = @EmployeeID,
		TransactionID = @TransactionID,
		OrderDate = @OrderDate,
		DispatchDate = @DispatchDate,
		
		BillingCountry = @BillingCountry,
		BillingCity = @BillingCity,
		BillingState = @BillingState,
		BillingAddressLine1 = @BillingAddressLine1,
		BillingAddressLine2 = @BillingAddressLine2,
		BillingPostalCode = @BillingPostalCode,
		
		ShippingCountry = @ShippingCountry,
		ShippingCity = @ShippingCity,
		ShippingState = @ShippingState,
		ShippingAddressLine1 = @ShippingAddressLine1,
		ShippingAddressLine2 = @ShippingAddressLine2,
		ShippingPostalCode = @ShippingPostalCode,
		
		Remarks = @Remarks
		
	WHERE OrderID = @OrderID
END
GO
/*-----------------------------------------------------------------------------------*/
/* Delete order */
CREATE PROCEDURE uspDeleteOrder
	@OrderID	UNIQUEIDENTIFIER
AS
	DELETE FROM tblOrders WHERE OrderID = @OrderID
GO
/*-----------------------------------------------------------------------------------*/
/* Dispatch Order */
CREATE PROCEDURE uspDispatchOrder
	@OrderID UNIQUEIDENTIFIER
AS
BEGIN
	UPDATE tblOrders
	SET [Status] = 3,
		DispatchDate = GETDATE()
	WHERE DispatchDate = GETDATE()
END
GO
/*-----------------------------------------------------------------------------------*/
/* Set dispatch date */
CREATE PROCEDURE uspSetDispatchDate
	@OrderID		UNIQUEIDENTIFIER,
	@EmployeeID		INT,
	@DispatchDate	DATETIME
AS
BEGIN
	UPDATE tblOrders
	SET EmployeeID = @EmployeeID,
		DispatchDate = @DispatchDate
	WHERE OrderID = @OrderID
END
GO

/*************************************************************************************/

/******************************** USP For Products ***********************************/
/* Get product */
CREATE PROCEDURE uspGetProductByID
	@ProductID CHAR(8)
AS
	SELECT * FROM vExtendedProducts WHERE ProductID = @ProductID	
GO
/*-----------------------------------------------------------------------------------*/
/* GetProducts */
CREATE PROCEDURE uspGetProducts

AS
BEGIN
	SELECT * FROM vExtendedProducts
END
GO
/*-----------------------------------------------------------------------------------*/
/* Get products by category */
CREATE PROCEDURE uspGetProductsByCategory
	@Category VARCHAR(30)
AS
BEGIN
	SELECT * FROM vExtendedProducts 
	WHERE Category IN (SELECT Child FROM vSubCategories WHERE Ancestor = @Category) OR
		  Category = @Category
END
GO
/*-----------------------------------------------------------------------------------*/
/* Insert product */
CREATE PROCEDURE uspInsertProduct
	@ProductID		CHAR(8), 	--Format: [a-z]{3}#####
	@ProductName	VARCHAR(30),   
	@Category		VARCHAR(30),
	@Description	TEXT,
	
	@Materials		VARCHAR(60),
	@WoodFinish		VARCHAR(30),
	@Length			DECIMAL(18,2),
	@Width			DECIMAL(18,2),
	@Height			DECIMAL(18,2),
	@Diameter		DECIMAL(18,2),
	
	@Image1			VARCHAR(100), --path of images
	
	@Price			DECIMAL(18,2),
	@Units			INT,
	
	@IsActive		BIT
AS
BEGIN
	INSERT INTO tblProducts 
	VALUES(@ProductID, @ProductName, @Category, @Description, @Materials, @WoodFinish, 
		   @Length, @Width, @Height, @Diameter, @Image1, 
	       @Price, @Units, @IsActive)
END
GO	 
/*-----------------------------------------------------------------------------------*/
/* Update product */
CREATE PROCEDURE uspUpdateProduct
	@ProductID		CHAR(8), 	--Format: [a-z]{3}#####
	@ProductName	VARCHAR(30),   
	@Category		VARCHAR(30),
	@Description	TEXT,
	
	@Materials		VARCHAR(60),
	@WoodFinish		VARCHAR(30),
	@Length			DECIMAL,
	@Width			DECIMAL(18,2),
	@Height			DECIMAL(18,2),
	@Diameter		DECIMAL(18,2),
	
	@Image1			VARCHAR(100), --path of images
	
	@Price			DECIMAL(18,2),
	
	@IsActive		BIT
AS
BEGIN
	UPDATE tblProducts 
	SET ProductName = @ProductName,
		Category = @Category,
		[Description] = @Description,
		Materials = @Materials,
		WoodFinish = @WoodFinish,
		[Length] = @Length,
		Width = @Width,
		Height = @Height,
		Diameter = @Diameter,
     
		Image1 = @Image1,
     
		Price = @Price,
		IsActive = @IsActive
	WHERE ProductID = @ProductID 
END
GO
/*-----------------------------------------------------------------------------------*/
/* Delete product */
CREATE PROCEDURE uspDeleteProduct
	@ProductID CHAR(8)
AS
	DELETE FROM tblProducts WHERE ProductID = @ProductID
GO
/*-----------------------------------------------------------------------------------*/
/* Transfer product */
CREATE PROCEDURE uspTransferProduct
	@ProductID	CHAR(8),
	@Units		INT
AS
BEGIN
	UPDATE tblProducts
	SET Units = @Units + (SELECT Units FROM tblProducts WHERE ProductID = @ProductID)
	WHERE ProductID = @ProductID
END
GO
/*-----------------------------------------------------------------------------------*/
/* Top Seller In Category*/
CREATE PROCEDURE uspGetTopSeller
	@Rank INT
AS
BEGIN
	SELECT TOP(@Rank) * FROM vExtendedProducts
	ORDER BY Sold DESC
END
GO
/*-----------------------------------------------------------------------------------*/
/* Top Seller In Category*/
CREATE PROCEDURE uspGetTopSellerInCategory
	@Category VARCHAR(30),
	@Rank INT
AS
BEGIN
	SELECT TOP(@Rank) * FROM vExtendedProducts 
	WHERE Category = @Category
	ORDER BY Sold DESC
END
GO

/*-----------------------------------------------------------------------------------*/
CREATE PROCEDURE uspSuggestProductsByCustomerID
	@CustomerID INT
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Suggests TABLE(CustomerID INT, ProductID CHAR(8))
	
	INSERT INTO @Suggests(CustomerID, ProductID) 
	SELECT CustomerID, ProductID FROM vCustomerPurchaseHistory
	WHERE CustomerID = (SELECT TOP 1 CustomerB FROM vDegrees WHERE CustomerA = @CustomerID ORDER BY Degree DESC)
	AND ProductID NOT IN (SELECT ProductID FROM vCustomerPurchaseHistory WHERE CustomerID = @CustomerID)
	
	SELECT * FROM vExtendedProducts WHERE ProductID IN (SELECT ProductID FROM @Suggests) ORDER BY Sold DESC
	
END
GO
/*-----------------------------------------------------------------------------------*/
CREATE PROCEDURE uspSuggestProductsByProductID
	@CustomerID INT,
	@ProductID CHAR(8)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @Customers TABLE (CustomerID INT) -- all customers who bought @ProductID
	DECLARE @ProductsRank TABLE (ProductID CHAR(8), [RANK] INT)
			
	INSERT INTO @Customers(CustomerID) SELECT CustomerID FROM vCustomerPurchaseHistory
	WHERE ProductID = @ProductID
	
	-- get number of customers from above table who bought each other product
	INSERT INTO @ProductsRank(ProductID, [Rank])
	SELECT ProductID, COUNT(CustomerID) FROM vCustomerPurchaseHistory
	WHERE ProductID <> @ProductID AND CustomerID IN (SELECT CustomerID FROM @Customers)
	GROUP BY ProductID
	ORDER BY COUNT(CustomerID)
	
	SELECT * FROM vExtendedProducts 
	WHERE ProductID IN (SELECT ProductID FROM @ProductsRank) 
	ORDER BY Sold DESC
END
GO

/*-----------------------------------------------------------------------------------*/
GO
Use master
